/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.itu.beans;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;

/**
 *
 * @author amin
 */
@ManagedBean
@SessionScoped
public class GuestDataBean implements Serializable{

    static final String url = "jdbc:mysql://localhost/guestbook";
    static Connection connection;
    List<GuestBean> guestList;
    private GuestBean guestBean;

    public GuestDataBean() {
        guestBean = new GuestBean();
    }

    public Connection getMySqlConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            try {
                connection = DriverManager.getConnection(url, "root", "opal71");

            } catch (SQLException ex) {
                Logger.getLogger(GuestDataBean.class.getName()).log(Level.SEVERE, null, ex);
            }


        } catch (ClassNotFoundException ex) {
            Logger.getLogger(GuestDataBean.class.getName()).log(Level.SEVERE, null, ex);
        }

        return connection;
    }

    public List<GuestBean> getGuestList() {

        try {
            String show = "SELECT first_name, last_name, email FROM guests";
            guestList = new ArrayList<GuestBean>();
            
            PreparedStatement ps = getMySqlConnection().prepareStatement(show);
            ResultSet results = ps.executeQuery();
            // get row data
            while (results.next()) {
                GuestBean guest = new GuestBean();
                guest.setFirstName(results.getString(1));
                guest.setLastName(results.getString(2));
                guest.setEmail(results.getString(3));
                guestList.add(guest);
            }

        } catch (Exception ex) {
            Logger.getLogger(GuestDataBean.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(GuestDataBean.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return guestList;

    }

    // insert a guest in guestbook database
    public void addGuest() {

        try {
            String add = "INSERT INTO guests ( "
                    + "first_name, last_name, email ) "
                    + "VALUES ( ?, ?, ? )";

            PreparedStatement ps = getMySqlConnection().prepareStatement(add);

            ps.setString(1, guestBean.getFirstName());
            ps.setString(2, guestBean.getLastName());
            ps.setString(3, guestBean.getEmail());

            ps.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(GuestDataBean.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(GuestDataBean.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

    }

    public GuestBean getGuestBean() {
        return guestBean;
    }

    public void setGuestBean(GuestBean guestBean) {
        this.guestBean = guestBean;
    }
}
